<?php

/**
 * A PHP reader of Aborior's Simplex database
 *
 * PHP version 5
 *
 * Simplex is now a defunct database format, so this package only supports
 * reading of database to recover data.
 *
 * @category  Database
 * @package   DB_Simplex
 * @author    Philippe Jausions <jausions@php.net>
 * @copyright 2008 by 11abacus / Philippe Jausions
 * @license   http://www.opensources.org/licenses/bsd-license.php BSD license
 * @version   CVS: $Id: $
 * @link      http://pear.11abacus.com/package/DB_Simplex
 */

require_once 'DB/Simplex/Exception.php';
require_once 'DB/Simplex/Result.php';

/**
 * A PHP reader of Aborior's Simplex database
 *
 * PHP version 5
 *
 * Simplex is now a defunct database format, so this package only supports
 * reading of database to recover data.
 *
 * @category  Database
 * @package   DB_Simplex
 * @author    Philippe Jausions <jausions@php.net>
 * @copyright 2008 by 11abacus / Philippe Jausions
 * @license   http://www.opensources.org/licenses/bsd-license.php BSD license
 * @version   Release: @package_version@
 * @link      http://pear.11abacus.com/package/DB_Simplex
 * @todo      Improve case-insensitiveness for table (and database) names
 */
class DB_Simplex
{
    /**
     * Path to databases
     *
     * @var string
     */
    protected $path;

    /**
     * Current database
     *
     * @var string
     */
    protected $database;

    /**
     * Full path to database
     *
     * @var string
     */
    protected $databasePath;

    /**
     * Information about databases/tables/indexes
     *
     * @var array of string
     */
    static protected $info = array(
    );
    
    /**
     * List of subfolder to look for to check if a folder contains a database
     *
     * @var array
     */
    static protected $subdirs = array(
        'forms', 
        'other', 
        'queries', 
        'reports', 
        'tables',
    );

    /**
     * Some regulat expression per type
     *
     * @var array
     */
    static protected $typeRegExp = array(
        'memo'              => '[a-zA-Z0-9 ]',
        'autointeger (seq)' => '[0-9 ]',
        'url'               => '.',
        'number'            => '[-0-9., ]',
        'integer'           => '[0-9 ]',
        'counter'           => '[0-9 ]',
        'ip address'        => '.',
        'text'              => '.',
        'email'             => '.',
        'password'          => '.',
        'true/false'        => '.',
        'date'              => '[0-9 ]',
        'time'              => '[-0-9 ]',
        'file'              => '[a-zA-Z0-9 ]',
        'handle'            => '.',
        'autointeger (ran)' => '[0-9 ]',
    );
     
    /**
     * Class constructor
     *
     * @param string $path     Full path to folder containing the databases
     * @param string $database Database name
     */
    public function __construct($path, $database = null)
    {
        $this->connect($path);
        if ($database !== null) {
            $this->selectDatabase($database);
        }
    }

    /**
     * Connects to the "server"
     *
     * @param string $path full path to folder containing the databases
     *
     * @return void
     */
    public function connect($path = null)
    {
        if ($path !== null) {
            $path = $this->normalizePath($path);
            if ($this->path !== $path) {
                $this->database     = null;
                $this->databasePath = null;
            }
            $this->path = $path;
        }
        if (!file_exists($path) || !is_dir($path)) {
            throw new DB_Simplex_Exception_IO('Could not find '.$path);
        }
        if (!($dir = opendir($path))) {
            throw new DB_Simplex_Exception_IO('Could not open: '.$path);
        }
        closedir($dir);
    }

    /**
     * Disconnects
     * 
     * @return void
     */
    public function disconnect()
    {
        $this->path         = null;
        $this->database     = null;
        $this->databasePath = null;
    }

    /**
     * Selects a database
     *
     * @param string $name database name
     *
     * @return string Previously selected database
     */
    public function selectDatabase($name)
    {
        if (!$this->isDatabase($this->path.$name)) {
            throw new DB_Simplex_Exception_NoSuchDatabase('Could not open database: '
                                                          .$name);
        }

        $previous = $this->database;
        
        $this->database     = $name;
        $this->databasePath = $this->normalizePath($this->path.$name);
        
        return $previous;
    }

    /**
     * Makes sure the path ends with a directory separator
     *
     * @param string $path Path to normalize
     *
     * @return string Normalized path
     */
    static protected function normalizePath($path)
    {
        $path = realpath($path);
        if (substr($path, -1) != DIRECTORY_SEPARATOR) {
            $path .= DIRECTORY_SEPARATOR;
        }
        return $path;
    }

    /**
     * Initialize information about databases
     *
     * @param string $databasePath Full path to database
     * 
     * @return void
     */
    protected static function initInfoDatabase($databasePath) 
    {
        if (!isset(self::$info[$databasePath])) {
            self::$info[$databasePath] = array(
                'loaded' => false,
                'tables' => array(),
            );
        }
    }

    /**
     * Initializes information about tables
     *
     * @param string $table Table name
     * 
     * @return void
     */
    protected function initInfoTable($table) 
    {
        $_table = strtolower($table);
        if (!isset(self::$info[$this->databasePath]['tables'][$_table])) {
            self::$info[$this->databasePath]['tables'][$_table] = array(
                'name'         => $table,
                'loadedFields' => false,
                'loadedFKeys'  => false,
                'fields'       => array(),
                'indexes'      => array(),
                'foreigns'     => array(),
            );
        }
    }

    /**
     * Checks if a folder is a database
     *
     * @param string $path full path to folder containing the database
     *
     * @return boolean TRUE if exists, FALSE otherwise
     */
    public static function isDatabase($path)
    {
        $path = self::normalizePath($path);
        if (isset(self::$info[$path])) {
            return true;
        }
        if (!file_exists($path) || !is_dir($path)) {
            return false;
        }
        
        if (!($dir = opendir($path))) {
            throw new DB_Simplex_Exception_IO('Could not open folder: '.$path);
        }
        closedir($dir);
        
        foreach (self::$subdirs as $subdir) {
            if (!file_exists($path.$subdir) || !is_dir($path.$subdir)) {
                return false;
            }
        }
        if (!file_exists($path.'other/keys.dat')) {
            return false;
        }

        self::initInfoDatabase($path);
        return true;
    }

    /**
     * Returns whether the database exists with the given name 
     *
     * @param string $name Database name
     * 
     * @return boolean TRUE/FALSE
     */
    public function databaseExists($name)
    {
        return self::isDatabase($this->path.$name);    
    }

    /**
     * Returns a list of databases
     *
     * @return array of database names
     */
    public function listDatabases()
    {
        if (($dirs = glob($this->path.'*', GLOB_ONLYDIR)) === false) {
            throw new DB_Simplex_Exception_IO('Could not scan for databases');
        }
        
        $dbs = array();
        foreach ($dirs as $dir) {
            $dir = basename($dir);
            try {
                if (self::isDatabase($this->path.$dir)) {
                    $dbs[] = $dir;
                }
            } catch (DB_Simplex_Exception $e) {
                // Ignore
            }
        }
        return $dbs;
    }

    /**
     * Returns a list of tables for the database
     *
     * @return array List of table names
     */
    public function listTables()
    {
        if ($this->database === null) {
            throw new DB_Simplex_Exception_NotConnected('No database selected');
        }
        
        if (self::$info[$this->databasePath]['loaded'] === true) {
            return array_keys(self::$info[$this->databasePath]['tables']); 
        }

        $path = $this->databasePath.DIRECTORY_SEPARATOR.'tables'
                .DIRECTORY_SEPARATOR.'*.th';
                
        if (($matches = glob($path)) === false) {
            throw new DB_Simplex_Exception_IO('Could not open database '
                                              .$this->database);
        }
                
        $tables = array();
        foreach ($matches as $file) {
            $table  = basename($file, '.th');
            $_table = strtolower($table);
            if (isset(self::$info[$this->databasePath]['tables'][$_table])) {
                continue;
            }
            if (is_file($file)) {
                // Check basic file format
                $def = file($file);
                if (strcasecmp(trim($def[0]), $table) != 0) {
                    throw new DB_Simplex_Exception_CorruptedData('Table '.$table
                                          .' definition file seems corrupted.');
                }
                $this->initInfoTable($table);
            }
        }
        self::$info[$this->databasePath]['loaded'] = true;
                
        return array_keys(self::$info[$this->databasePath]['tables']);
    }

    /**
     * Returns whether the table already exists in the current database
     *
     * @param string $table Table name to check
     * 
     * @return boolean TRUE if exists, FALSE otherwise
     */
    public function tableExists($table)
    {
        if ($this->database === null) {
            throw new DB_Simplex_Exception_NotConnected('No database selected');
        }
        $_table = strtolower($table);
        if (!empty(self::$info[$this->databasePath]['tables'][$_table])) {
            return true;
        }
        if (preg_match('/^[a-z0-9A-Z_]+$/', $table) === false) {
            throw new DB_Simplex_Exception_InvalidData('Invalid table name');
        }

        $this->listTables();
        if (empty(self::$info[$this->databasePath]['tables'][$_table])) {
            return false;
        }
        return true;
    }

    /**
     * Returns whether a field exists in a table
     *
     * @param string $table Table name
     * @param string $field Field name
     * 
     * @return boolean TRUE if exists, FALSE otherwise
     */
    public function fieldExists($table, $field)
    {
        $fields = $this->listFields($table);
        return isset($fields[strtolower($field)]);
    }

    /**
     * Returns fields information for the table
     *
     * @param string $table table name to analyze
     * 
     * @return array indexed by field name and value is array with the following
     *               indexes: Field, Type, Length, Comment, Default, On
     */
    public function listFields($table)
    {
        if (!$this->tableExists($table)) {
            throw new DB_Simplex_Exception_NoSuchTable('No such table: '
                                                       .$table);        
        }

        $_table = strtolower($table);
        $info   =& self::$info[$this->databasePath]['tables'][$_table]; 
        if ($info['loadedFields'] === true) {
            return $info['fields'];
        }

        $table = $info['name'];
        $def   = file($this->databasePath.'tables'
                      .DIRECTORY_SEPARATOR.$table.'.th');

        $fields = array();
        for ($i = 3; $i < count($def); ++$i) {
            list($name, $type, $length, $comment, $default, $on) = explode('[|]',
                                                                 trim($def[$i]));

            $fields[strtolower(trim($name))] = array(
                'Field'    => trim($name),
                'Type'     => trim($type),
                'Length'   => trim($length),
                'Comment'  => trim($comment),
                'Default'  => trim($default), // ??
                'Required' => (boolean)$on,
            );     
        }

        $info['fields']       = $fields;
        $info['loadedFields'] = true;

        return $fields;
    }

    /**
     * Returns a list of indexes
     *
     * @param string $table Table name
     * 
     * @return array list of indexes 
     */
    public function listIndexes($table)
    {
        $indexes = array();
        $fields  = $this->listFields($table);
        $foreign = $this->listForeignKeys($table);
        foreach ($fields as $field) {
            if ($field['Type'] === 'AutoInteger (Seq)') {
                $indexes[$field['Field']] = $field['Field'];
            }
        }

        /*
        if (!strcasecmp($table, 'Article')) {
            echo __FILE__.' ['.__LINE__."] $table: \n";
            print_r($foreign);
            print_r(self::$info);
            exit;
        }
        */
        
        foreach ($foreign as $key) {
            $indexes[$key['Field']] = $key['Field'];  
        }

        return array_values($indexes);
    }

    /**
     * Checks whether an index exists
     *
     * @param string $table Table name
     * @param string $index Index name
     * 
     * @return boolean TRUE if exists, FALSE otherwise
     */
    public function indexExists($table, $index)
    {
        return in_array($index, $this->listIndexes($table));
    }

    /**
     * Returns information about a constraint
     *
     * @param string $table Table nam
     * @param string $index Index name
     *  
     * @return array
     */
    public function getConstraint($table, $index)
    {        
        $fields  = $this->listFields($table);
        $foreign = $this->listForeignKeys($table);
        $found   = false;
        
        $def = array(
            'primary'    => false,
            'foreign'    => false,
            'fields'     => array(),
            'references' => array(),
        );
        foreach ($fields as $field) {
            if ($field['Field'] === $index
                && $field['Type'] === 'AutoInteger (Seq)') {

                $def['primary'] = true;
                
                $found = true;
                break;
            }
        }
        foreach ($foreign as $fk) {
            if ($fk['Field'] === $index) {
                $def['foreign']    = true;
                $def['references'] = array(
                    'table'  => $fk['References'][0],
                    'fields' => array(
                        $fk['References'][1] => array(
                            'position' => 1,
                        ),
                    ),  
                );

                $found = true;
                break;
            }
        }

        if ($found === false) {
            throw new DB_Simplex_Exception_NoSuchIndex('No such index '.$index
                .' for table '.$table);
        }
        $def['fields'] = array($index => array());
        //echo __FILE__.' ['.__LINE__.'] '.__FUNCTION__.'('.$table.', '.$index.") \n";
        //print_r($def);
        
        return $def;
    }

    /**
     * Returns a list of foreign keys definitions
     *
     * @param string $table table name
     * 
     * @return array local column => foreign column
     */
    public function listForeignKeys($table)
    {
        $_table = strtolower($table);
        $dbPath = $this->databasePath;
        if (!empty(self::$info[$dbPath]['tables'][$_table]['loadedFKeys'])) {
            return self::$info[$dbPath]['tables'][$_table]['foreigns'];
        }

        $tables = array_change_key_case(array_flip($this->listTables()), 
                                        CASE_LOWER);
        if (!isset($tables[$_table])) {
            throw new DB_Simplex_Exception_NoSuchTable('No such table: '
                                                       .$table);        
        }

        $def = file($dbPath.'other'.DIRECTORY_SEPARATOR.'keys.dat');
        if ($def === false) {
            throw new DB_Simplex_Exception_IO('Could not read keys.dat for table'
                                              .$table);
        }

        $keys = array();
        $rx   = '/^([^\]]+)\.([^\]]+)\[\|\]([^.]+)\.([^\]]+)\[\|\]$/';

        foreach ($def as $line) {

            if (trim($line) === '' || !preg_match($rx, $line, $match)) {
                continue;
            }
            if (strcasecmp($_table, $match[1]) 
                && !isset($tables[strtolower($match[1])])) {
                throw new DB_Simplex_Exception_CorruptedData('Reference to non-existing table '
                                         .$match[1].' in keys.dat');
            }
            if (strcasecmp($_table, $match[2]) 
                && !isset($tables[strtolower($match[3])])) {
                throw new DB_Simplex_Exception_CorruptedData('Reference to non-existing table '
                                         .$match[3].' in keys.dat');
            }
            if (!$this->fieldExists($match[1], $match[2])) {
                throw new DB_Simplex_Exception_CorruptedData('Reference to non-existing field '
                                         .$match[1].'.'.$match[2].' in keys.dat');
            }
            if (!$this->fieldExists($match[3], $match[4])) {
                throw new DB_Simplex_Exception_CorruptedData('Reference to non-existing field '
                                         .$match[3].'.'.$match[4].' in keys.dat');
            }
            //echo __FILE__.' ['.__LINE__."] $table: $line\n";
            $keys[strtolower($match[1])][strtolower($match[2])] = array(
                'Field'      => $match[2],
                'References' => array($match[3], $match[4]),
            );
        }
        
        // Store the information we found
        foreach ($tables as $t => $i) {
            if (isset($keys[$t])) {
                self::$info[$dbPath]['tables'][$t]['foreigns'] = $keys[$t];
            }
            self::$info[$dbPath]['tables'][$t]['loadedFKeys'] = true;
        }

        return self::$info[$dbPath]['tables'][$_table]['foreigns'];
    }

    /**
     * Runs a basic "SELECT fields FROM table" SQL query
     * 
     * Only very basic SELECT queries are supported:
     * <ul>
     *  <li>SELECT [field1], [field2], [field...] FROM [table]</li>
     *  <li>SELECT * FROM [table]</li>
     * </ul>
     *
     * @param string $sql SQL query
     * 
     * @return DB_Simplex_Result instance
     */
    public function query($sql)
    {
        $regexp = '/^\s*SELECT\s+(.+)\s+FROM\s+(\S+)\s*$/im';
        if (!preg_match($regexp, $sql, $matches)) {
            throw new DB_Simplex_Exception_Unsupported('Only basic SELECT queries are supported');
        }
        $fields = explode(',', $matches[1]);
        $table  = $matches[2];
        
        if (!$this->tableExists($table)) {
            throw new DB_Simplex_Exception_NoSuchTable('No such table: '.
                $table);
        }
        $tableFields = $this->listFields($table);
        $tableFields = array_change_key_case($tableFields, CASE_LOWER);

        $selectFields = array(); 
        foreach ($fields as $i => $field) {
            $field = trim($field);

            $fields[$i] = $field;
            if ($field == '*') {
                foreach ($tableFields as $field) {
                    $selectFields[] = array($table, $field['Field'], 
                                            $field['Field']);
                }
            } elseif (!isset($tableFields[strtolower($field)])) {
                throw new DB_Simplex_Exception_NoSuchField('No such field: '
                    .$field);
            } else {
                $selectFields[] = array($table, $field, $field);
            }
        }
        
        return $this->readTable($table, $selectFields);
    }
    
    /**
     * Loads table content
     *
     * @param string $table        Table name
     * @param array  $selectFields List of table/fields to read
     *   
     * @return DB_Simplex_Result instance
     */
    protected function readTable($table, $selectFields)
    {
        $_table = strtolower($table);
        $table  = self::$info[$this->databasePath]['tables'][$_table]['name'];

        $file = $this->databasePath.'tables'.DIRECTORY_SEPARATOR.$table.'.td';

        $regexp = $this->getTableRowRegExp($table, $fieldsRead);

        $data = file($file);
        foreach ($data as $i => $row) {
            if (!preg_match($regexp, $row, $matches)) {
                throw new DB_Simplex_Exception_CorruptedData('Data in table "'
                    .$table.'" are corrupted at line '.($i + 1));
            }
            array_shift($matches);
            $data[$i] = $matches;
        }

        // Associates index in $matches to field definition and selected fields
        $fieldsInfo = array();
        $i = 0;
        foreach ($fieldsRead as $field => $info) {
            $info['Table']  = $table;
            $fieldsInfo[$i] = $info;
            foreach ($selectFields as $j => $info) {
                if (count($info) < 4
                    && strcasecmp($info[0], $_table) == 0
                    && strcasecmp($info[1], $field) == 0) {
                    $selectFields[$j][3] = $i;
                }
            }
            ++$i;
        }
        
        $result = new DB_Simplex_Result($data, $fieldsInfo, $selectFields);
    }

    /**
     * Returns the regular expression to parse a row in the data file
     *
     * @param string $table      Table name
     * @param array  &$fieldRead List of fields read
     * 
     * @return string the regular expression
     */
    protected function getTableRowRegExp($table, &$fieldsRead)
    {
        $fieldsRead = $this->listFields($table);
        
        $rx = '/^';
        foreach ($fieldsRead as $field) {
            $rx .= '('.self::$typeRegExp[strtolower($field['Type'])]
                   .'{'.((int)$field['Length']).'})';
        }
        return $rx.'\s*$/';
    }
}

?>